package org.gdstash.db;

import org.gdstash.file.ARCDecompress;
import org.gdstash.file.ARZDecompress;
import org.gdstash.file.ARZRecord;
import org.gdstash.ui.GDStashFrame;
import org.gdstash.util.GDConstants;
import org.gdstash.util.GDMsgFormatter;
import org.gdstash.util.GDMsgLogger;

import java.awt.image.BufferedImage;
import java.nio.ByteBuffer;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;
import java.util.concurrent.ConcurrentHashMap;

public class DBClassTable {
    public static final String TABLE_NAME = "GDC_CLASSTABLE";
    private static final int ROW_CLASSTABLE_ID = 1;
    private static final int ROW_MASTERY_BAR_ID = 2;
    private static final int ROW_SKILL_PANE_ID = 3;
    private static final int ROW_MASTERY_BITMAP_ID = 4;
    private static final int ROW_MASTERY_ID = 5;
    private static final int ROW_TITLE_TAG = 6;
    private static final int ROW_TITLE_TEXT_MALE = 7;
    private static final int ROW_TITLE_TEXT_FEMALE = 8;
    private static ConcurrentHashMap<String, DBClassTable> hashBuffer;
    private static ConcurrentHashMap<String, String> hashID;
    private String classTableID;
    private String masteryBarID;
    private String skillPaneID;
    private String masteryBitmapID;
    private String masteryID;
    private String titleTag;
    private String titleMale;
    private String titleFemale;
    private DBBitmap dbMasteryBar;
    private DBBitmap dbSillPane;
    private DBBitmap dbMasteryBitmap;
    private List<DBClassTableButton> buttons;

    public DBClassTable() {
        this.buttons = new LinkedList<DBClassTableButton>();
    }

    public DBClassTable(final ARZRecord record) {
        this();
        this.classTableID = record.getFileName();
        this.masteryBarID = record.getMasteryBarID();
        this.skillPaneID = record.getSkillPaneID();
        this.masteryBitmapID = record.getMasteryBitmapID();
        this.setTitleTag(record.getTitleTag());
        String id1 = null;
        String id2 = null;
        int i = 1;
        for (final String s : record.getSkillButtonList()) {
            final DBClassTableButton button = new DBClassTableButton(this.classTableID, s, i);
            this.buttons.add(button);
            if (i == 1) {
                id1 = s;
            }
            if (s.contains("classtraining")) {
                id2 = s;
            }
            if (id2 != null) {
                this.masteryID = id2;
            } else {
                this.masteryID = id1;
            }
            ++i;
        }
    }

    static {
        DBClassTable.hashBuffer = new ConcurrentHashMap<String, DBClassTable>();
        DBClassTable.hashID = new ConcurrentHashMap<String, String>();
    }

    private static List<DBClassTable> wrap(final ResultSet rs) throws SQLException {
        final LinkedList<DBClassTable> list = new LinkedList<DBClassTable>();
        final Blob blob = null;
        while (rs.next()) {
            final DBClassTable ct = new DBClassTable();
            ct.classTableID = rs.getString(1);
            final DBClassTable buff = DBClassTable.hashBuffer.get(ct.classTableID);
            if (buff != null) {
                list.add(buff);
            } else {
                ct.masteryBarID = rs.getString(2);
                ct.skillPaneID = rs.getString(3);
                ct.masteryBitmapID = rs.getString(4);
                ct.masteryID = rs.getString(5);
                ct.titleTag = rs.getString(6);
                ct.titleMale = rs.getString(7);
                ct.titleFemale = rs.getString(8);
                ct.buttons = DBClassTableButton.getButtons(ct.classTableID);
                if (ct.masteryBarID != null) {
                    ct.dbMasteryBar = DBBitmap.get(ct.masteryBarID);
                }
                if (ct.skillPaneID != null) {
                    ct.dbSillPane = DBBitmap.get(ct.skillPaneID);
                }
                if (ct.masteryBitmapID != null) {
                    ct.dbMasteryBitmap = DBBitmap.get(ct.masteryBitmapID);
                }
                if (ct.titleMale != null) {
                    final ByteBuffer buffer = StandardCharsets.UTF_8.encode(ct.titleMale);
                    final String utf8 = StandardCharsets.UTF_8.decode(buffer).toString();
                    ct.titleMale = utf8;
                }
                if (ct.titleFemale != null) {
                    final ByteBuffer buffer = StandardCharsets.UTF_8.encode(ct.titleFemale);
                    final String utf8 = StandardCharsets.UTF_8.decode(buffer).toString();
                    ct.titleFemale = utf8;
                }
                list.add(ct);
                DBClassTable.hashBuffer.put(ct.classTableID, ct);
            }
        }
        return list;
    }

    public BufferedImage getMasteryBar() {
        if (this.dbMasteryBar == null) {
            return null;
        }
        return this.dbMasteryBar.getImage();
    }

    public DBBitmap getMasteryBarDB() {
        return this.dbMasteryBar;
    }

    public BufferedImage getSkillPane() {
        if (this.dbSillPane == null) {
            return null;
        }
        return this.dbSillPane.getImage();
    }

    public DBBitmap getSkillPaneDB() {
        return this.dbSillPane;
    }

    public BufferedImage getMasteryBitmap() {
        if (this.dbMasteryBitmap == null) {
            return null;
        }
        return this.dbMasteryBitmap.getImage();
    }

    public DBBitmap getMasteryBitmapDB() {
        return this.dbMasteryBitmap;
    }

    public String getMasteryID() {
        return this.masteryID;
    }

    public List<DBClassTableButton> getButtonList() {
        return this.buttons;
    }

    public static void clearBuffer() {
        DBClassTable.hashBuffer.clear();
        DBClassTable.hashID.clear();
    }

    public static void createTables() throws SQLException {
        final String dropTable = "DROP TABLE GDC_CLASSTABLE";
        final String createTable = "CREATE TABLE GDC_CLASSTABLE (CLASSTABLE_ID      VARCHAR(256) NOT NULL, MASTERY_BAR_ID     VARCHAR(256), SKILLPANE_ID       VARCHAR(256), MASTERY_BITMAP_ID  VARCHAR(256), MASTERY_ID         VARCHAR(256), TITLE_TAG          VARCHAR(64), TITLE_MALE         VARCHAR(64), TITLE_FEMALE       VARCHAR(64), PRIMARY KEY (CLASSTABLE_ID))";
        try (final Connection conn = GDDBData.getConnection()) {
            final boolean auto = conn.getAutoCommit();
            conn.setAutoCommit(false);
            try (final Statement st = conn.createStatement()) {
                if (GDDBUtil.tableExists(conn, "GDC_CLASSTABLE")) {
                    st.execute(dropTable);
                }
                st.execute(createTable);
                st.close();
                conn.commit();
                DBClassTableButton.createTable(conn);
            } catch (SQLException ex) {
                conn.rollback();
                final Object[] args = {"GDC_CLASSTABLE"};
                final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_CREATE_TABLE", args);
                GDMsgLogger.addError(msg);
                throw ex;
            } finally {
                conn.setAutoCommit(auto);
            }
        }
    }

    public static void insert(final ARZRecord record) throws SQLException {
        final DBClassTable entry = get(record.getFileName());
        if (entry != null) {
            return;
        }
        final DBClassTable ct = new DBClassTable(record);
        final String insertItem = "INSERT INTO GDC_CLASSTABLE VALUES (?,?,?,?,?,?,?,?)";
        try (final Connection conn = GDDBData.getConnection()) {
            final boolean auto = conn.getAutoCommit();
            conn.setAutoCommit(false);
            try (final PreparedStatement ps = conn.prepareStatement(insertItem)) {
                ps.setString(1, ct.classTableID);
                ps.setString(2, ct.masteryBarID);
                ps.setString(3, ct.skillPaneID);
                ps.setString(4, ct.masteryBitmapID);
                ps.setString(5, ct.masteryID);
                ps.setString(6, ct.titleTag);
                ps.setString(7, ct.titleMale);
                ps.setString(8, ct.titleFemale);
                ps.executeUpdate();
                ps.close();
                DBClassTableButton.insertButtons(conn, ct.classTableID, ct.buttons);
                conn.commit();
            } catch (SQLException ex) {
                conn.rollback();
                final Object[] args = {record.getFileName(), "GDC_CLASSTABLE"};
                final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_INS_TABLE_BY_ID", args);
                GDMsgLogger.addLowError(msg);
                GDMsgLogger.addLowError(ex);
            } finally {
                conn.setAutoCommit(auto);
            }
        } catch (SQLException ex2) {
            throw ex2;
        }
    }

    public static DBClassTable getByMasteryID(final String masteryID) {
        String classTableID = DBClassTable.hashID.get(masteryID);
        if (classTableID != null) {
            return get(classTableID);
        }
        final String command = "SELECT GDC_CLASSTABLE.CLASSTABLE_ID FROM GDC_CLASSTABLE, GDC_SKILL_MASTER WHERE GDC_CLASSTABLE.MASTERY_ID = ? AND GDC_CLASSTABLE.CLASSTABLE_ID = GDC_SKILL_MASTER.CLASSTABLE_ID";
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            ps.setString(1, masteryID);
            try (final ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    classTableID = rs.getString(1);
                }
                conn.commit();
            } catch (SQLException ex) {
                throw ex;
            }
        } catch (SQLException ex2) {
            final Object[] args = {masteryID, "GDC_CLASSTABLE"};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex2);
        }
        if (classTableID == null) {
            return null;
        }
        DBClassTable.hashID.put(masteryID, classTableID);
        return get(classTableID);
    }

    public static DBClassTable get(final String classTableID) {
        DBClassTable ct = null;
        ct = DBClassTable.hashBuffer.get(classTableID);
        if (ct == null) {
            ct = getDB(classTableID);
        }
        return ct;
    }

    public static List<DBClassTable> getAll() {
        final String command = "SELECT CLASSTABLE_ID FROM GDC_CLASSTABLE";
        final List<DBClassTable> cTabs = new LinkedList<DBClassTable>();
        final List<String> ctIDs = new LinkedList<String>();
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            try (final ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    final String s = rs.getString(1);
                    ctIDs.add(s);
                }
            } catch (SQLException ex) {
                throw ex;
            }
            conn.commit();
            for (final String ctID : ctIDs) {
                final DBClassTable ct = get(ctID);
                cTabs.add(ct);
            }
        } catch (SQLException ex2) {
            final Object[] args = {"-", "GDC_CLASSTABLE"};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex2);
        }
        return cTabs;
    }

    private static DBClassTable getDB(final String classTableID) {
        DBClassTable ct = null;
        final String command = "SELECT * FROM GDC_CLASSTABLE WHERE CLASSTABLE_ID = ?";
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            ps.setString(1, classTableID);
            try (final ResultSet rs = ps.executeQuery()) {
                final List<DBClassTable> list = wrap(rs);
                if (list.isEmpty()) {
                    ct = null;
                } else {
                    ct = list.get(0);
                }
                conn.commit();
            } catch (SQLException ex) {
                throw ex;
            }
        } catch (SQLException ex2) {
            final Object[] args = {classTableID, "GDC_CLASSTABLE"};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex2);
        }
        return ct;
    }

    public String getMasteryTitle(final int genderCode) {
        String title = this.titleMale;
        if (genderCode == 1) {
            title = this.titleFemale;
        }
        return title;
    }

    private void setTitleTag(final String titleTag) {
        this.titleTag = titleTag;
        if (titleTag == null) {
            this.titleMale = null;
            this.titleFemale = null;
        } else {
            this.titleMale = GDStashFrame.arcList.getTag(ARCDecompress.FileModule.All, GDConstants.TAG_TEXT_SKILLS, titleTag, false);
            if (this.titleMale != null) {
                final String[] genders = ARZDecompress.getGenderTexts(this.titleMale);
                this.titleMale = genders[0];
                this.titleFemale = genders[1];
            }
        }
    }
}
